package cn.edu.niit.alienlab.jshttplessonserver.dao;

import cn.edu.niit.alienlab.jshttplessonserver.domain.MovieInfo;
import cn.edu.niit.alienlab.jshttplessonserver.dto.CategoryDto;
import cn.edu.niit.alienlab.jshttplessonserver.dto.MovieInfoDto;
import cn.edu.niit.alienlab.jshttplessonserver.dto.YearDto;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface MovieDao {
    @Select("SELECT ti.type_id,ti.`name`,count(mt.id) movie_count FROM type_info ti,movie_types mt " +
            "WHERE ti.type_id = mt.type_id " +
            "GROUP BY ti.type_id,ti.`name` " +
            "ORDER BY COUNT(ti.type_id) DESC")
    List<CategoryDto> findAllCategory();

    @Select("SELECT DISTINCT movie_year FROM movie_info ORDER BY movie_year DESC")
    List<YearDto> findAllYear();

    @Select("<script>" +
            "SELECT DISTINCT tb.movie_id,NAME,name_en,img,big_image,title_others,rating,director_id,person_count,story,rating_count,LENGTH,movie_publish_date,movie_year,tb.type_name,tb.director_name,actordb.actors_name FROM (" +
            "SELECT DISTINCT mi.*,mt.type_id,typedb.type_name,di.`name` director_name FROM " +
            "movie_info mi,movie_types mt,  " +
            "            (SELECT a.movie_id,GROUP_CONCAT(b.name) type_name FROM movie_types a,type_info b  " +
            "            WHERE a.type_id=b.type_id  " +
            "            GROUP BY a.movie_id) typedb,  " +
            "            director_info di " +
            "WHERE mi.movie_id = mt.movie_id AND typedb.movie_id=mi.movie_id AND di.`director_id`=mi.`director_id` " +
            ") tb LEFT JOIN (SELECT a.movie_id,GROUP_CONCAT(b.name) actors_name FROM movie_actors a,actor_info b  " +
            "            WHERE a.actor_id=b.actor_id AND b.name IS NOT NULL AND b.name!=''  " +
            "            GROUP BY a.movie_id) actordb  ON actordb.movie_id=tb.movie_id where 1=1 " +
            "<if test='keyword != null'>AND tb.name like CONCAT('%',#{keyword},'%')</if>" +
            "<if test='movie_year != null'>AND tb.movie_year = #{movie_year}</if>" +
            "<if test='categories != null'>AND tb.type_id IN " +
            "<foreach collection='categories' item='category' open='(' close=')' separator=','>" +
            "#{category}" +
            "</foreach>" +
            "</if>" +
            "limit #{pageNum},#{pageSize}"+
            "</script>")
    List<MovieInfoDto> findMovieList(@Param("keyword") String keyword, @Param("movie_year") Integer movie_year, @Param("categories") String[] categories,@Param("pageNum") int pageNum,
                                     @Param("pageSize") int pageSize);

    @Select("<script>" +
            "SELECT count(1) total_count FROM (" +
            "SELECT DISTINCT tb.movie_id,NAME,name_en,img,big_image,title_others,rating,director_id,person_count,story,rating_count,LENGTH,movie_publish_date,movie_year,tb.type_name,tb.director_name,actordb.actors_name FROM ("+
            "SELECT DISTINCT mi.*,mt.type_id,typedb.type_name,di.`name` director_name FROM " +
            "movie_info mi,movie_types mt,  " +
            "            (SELECT a.movie_id,GROUP_CONCAT(b.name) type_name FROM movie_types a,type_info b  " +
            "            WHERE a.type_id=b.type_id  " +
            "            GROUP BY a.movie_id) typedb,  " +
            "            director_info di " +
            "WHERE mi.movie_id = mt.movie_id AND typedb.movie_id=mi.movie_id AND di.`director_id`=mi.`director_id` " +
            ") tb LEFT JOIN (SELECT a.movie_id,GROUP_CONCAT(b.name) actors_name FROM movie_actors a,actor_info b  " +
            "            WHERE a.actor_id=b.actor_id AND b.name IS NOT NULL AND b.name!=''  " +
            "            GROUP BY a.movie_id) actordb  ON actordb.movie_id=tb.movie_id where 1=1 " +
            "<if test='keyword != null'>AND tb.name like CONCAT('%',#{keyword},'%')</if>" +
            "<if test='movie_year != null'>AND tb.movie_year = #{movie_year}</if>" +
            "<if test='categories != null'>AND tb.type_id IN " +
            "<foreach collection='categories' item='category' open='(' close=')' separator=','>" +
            "#{category}" +
            "</foreach>" +
            "</if>" +
            ") tabletemp"+
            "</script>")
    Integer findMovieListCount(@Param("keyword") String keyword, @Param("movie_year") Integer movie_year, @Param("categories") String[] categories);


    @Insert("INSERT INTO `movie_info` (`movie_id`,`name`,`name_en`,`img`,`title_others`,`rating`,`director_id`,`person_count`,`story`,`rating_count`,`length`,`movie_publish_date`,`movie_year`) " +
            " VALUES(#{movieId},#{name},#{nameEn},#{img},#{titleOthers},#{rating},#{directorId},#{personCount},#{story},#{ratingCount},#{length},#{moviePublishDate},#{movieYear})")
    Integer createMovie(MovieInfo movieInfo);

    @Update("update `movie_info` set name=#{name},name_en=#{nameEn},img=#{img},title_others=#{titleOthers},director_id=#{directorId}," +
            " person_count=#{personCount},story=#{story},rating_count=#{ratingCount},length=#{length},movie_publish_date=#{moviePublishDate},movie_year=#{movieYear} where movie_id=#{movieId}")
    Integer updateMovie(MovieInfo movieInfo);

    @Insert("insert into movie_types(movie_id,type_id) values(#{movieId},#{typeId})")
    Integer addMovieType(@Param("movieId") Integer movieId,@Param("typeId") Integer typeId);

    @Delete("delete from movie_types where movie_id=#{movieId}")
    Integer deleteMovieType(Integer movieId);

    @Delete("delete from movie_info where movie_id=#{movieId}")
    Integer deleteMovie(Integer movieId);
}
